﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.Utils.Drawing;
using DevExpress.Utils.Frames;
using DevExpress.XtraEditors.Repository;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.BandedGrid;
using DevExpress.XtraGrid.Views.Base;
using DevExpress.XtraGrid.Views.Grid;
using HorizontalMerging;
using System.Configuration;
using DevExpress.XtraCharts;
using DevExpress.Utils.DPI;
using DevExpress.Utils.Text;
using System.Xml.Serialization;
using DevExpress.Utils.Filtering.Internal;
using DevExpress.Charts.Model;
using ChartTitle = DevExpress.XtraCharts.ChartTitle;
using System.Runtime.CompilerServices;

namespace 富诚_库存账龄分析看板
{
    public partial class fcStockAge : Form
    {
        string TimeRefresh, OrganizationName;
        int TableDay1, TableDay2, TableDay3, TableDay4, TableMonth, LineYear, LineMonth, BarYear, BarMonth, BarDay;
        
        DataTable dtViewAll = new DataTable();  //表数据
        DataTable dtView = new DataTable();     //显示界面数据
        int dtViewRowsIndex = 10, LineNow;    //单页行数，当前行数

        //实例化“连接对象”
        SqlConnection conn = new SqlConnection("Data Source=192.168.0.197;Initial Catalog=AIS20201015150818;User ID=sa;Password=sa123");

        public fcStockAge()
        {
            InitializeComponent();
        }

        //初始化加载
        [Obsolete]
        private void Form1_Load(object sender, EventArgs e)
        {
            this.FormBorderStyle = FormBorderStyle.None;//无边框
            this.WindowState = FormWindowState.Maximized;

            Setting.Visible = false;
            Exit.Visible = false;

            #region 窗体位置
            //Button
            Exit.Left = this.Width - Setting.Width * 3/2;
            Setting.Left = this.Exit.Left - Setting.Width;
            //gridcontrol1位置
            gridControl1.Height = this.Height / 2 - 8;
            //chartcontro{1,2}大小、位置
            chartControl1.Top = gridControl1.Height;
            chartControl2.Top = gridControl1.Height;
            chartControl1.Left = 0;
            chartControl2.Left = this.Width / 2;
            int chartWidth = this.Width / 2;
            int chartHeight = this.Height - gridControl1.Height;
            chartControl1.Size = new Size(chartWidth, chartHeight);
            chartControl2.Size = new Size(chartWidth, chartHeight);
            #endregion

            //查询
            TimeRefresh = ConfigurationManager.AppSettings["TimeRefresh"].ToString();
            OrganizationName = ConfigurationManager.AppSettings["Organization"].ToString();
            TableDay1 = int.Parse(ConfigurationManager.AppSettings["TableDay1"]);
            TableDay2 = int.Parse(ConfigurationManager.AppSettings["TableDay2"]);
            TableDay3 = int.Parse(ConfigurationManager.AppSettings["TableDay3"]);
            TableDay4 = int.Parse(ConfigurationManager.AppSettings["TableDay4"]);
            TableMonth = int.Parse(ConfigurationManager.AppSettings["TableMonth"]);
            LineYear = int.Parse(ConfigurationManager.AppSettings["LineYear"]);
            LineMonth = int.Parse(ConfigurationManager.AppSettings["LineMonth"]);
            BarYear = int.Parse(ConfigurationManager.AppSettings["BarYear"]);
            BarMonth = int.Parse(ConfigurationManager.AppSettings["BarMonth"]);
            BarDay = int.Parse(ConfigurationManager.AppSettings["BarDay"]);

            GetDataTable(OrganizationName, TableDay1, TableDay2, TableDay3, TableDay4, TableMonth);
            GetDataLine(OrganizationName, LineYear, LineMonth);
            GetDataBar(OrganizationName, BarYear, BarMonth, BarDay);
        }

        #region 定时事件
        //定时翻页
        private void TimerPage_Tick(object sender, EventArgs e)
        {
            TimerPage.Interval = 5000;

            dtView.Clear();
            dtView = dtViewAll.Clone();

            //判断数据是否还有21行
            if (dtViewAll.Rows.Count > LineNow + dtViewRowsIndex)
            {
                for (int i = LineNow; i < LineNow + dtViewRowsIndex; i++)
                {
                    dtView.Rows.Add(dtViewAll.Rows[i].ItemArray); //添加数据行
                }

                gridControl1.DataSource = dtView;
                gridControl1.ForceInitialize();
                

                LineNow += dtViewRowsIndex;
            }
            else
            {
                for (int i = LineNow; i < dtViewAll.Rows.Count; i++)
                {
                    dtView.Rows.Add(dtViewAll.Rows[i].ItemArray); //添加数据行
                }

                gridControl1.DataSource = dtView;
                gridControl1.ForceInitialize();

                LineNow = 0;
            }

        }

        //定时刷新
        [Obsolete]
        private void TimerRefresh_Tick(object sender, EventArgs e)
        {
            TimerRefresh.Interval = 1000;
            Setting.Visible = false;
            Exit.Visible = false;

            if (DateTime.Now.ToLongTimeString().ToString() == TimeRefresh.ToString())
            {
                //查询
                GetDataTable(OrganizationName, TableDay1, TableDay2, TableDay3, TableDay4, TableMonth);
                GetDataLine(OrganizationName, LineYear, LineMonth);
                GetDataBar(OrganizationName, BarYear, BarMonth, BarDay);
            }
        }
        #endregion

        //行高
        private void bandedGridView1_CalcRowHeight(object sender, RowHeightEventArgs e)
        {
            if (e.RowHandle >= 0)
                e.RowHeight = 46;
        }

        //鼠标移动显示按钮
        private void gridControl1_MouseMove(object sender, MouseEventArgs e)
        {
            Setting.Visible = true;
            Exit.Visible = true;
        }

        //退出按钮
        private void Exit_Click(object sender, EventArgs e)
        {
            Application.Exit(); //退程序
        }

        //设置按钮
        [Obsolete]
        private void Setting_Click(object sender, EventArgs e)
        {
            Setting f1 = new Setting();
            f1.ShowDialog();
            ConfigurationManager.RefreshSection("appSettings"); //刷新数据
            if (f1.DialogResult == DialogResult.OK)
            {
                TimeRefresh = ConfigurationManager.AppSettings["TimeRefresh"].ToString();
                OrganizationName = ConfigurationManager.AppSettings["Organization"].ToString();
                TableDay1 = int.Parse(ConfigurationManager.AppSettings["TableDay1"]);
                TableDay2 = int.Parse(ConfigurationManager.AppSettings["TableDay2"]);
                TableDay3 = int.Parse(ConfigurationManager.AppSettings["TableDay3"]);
                TableDay4 = int.Parse(ConfigurationManager.AppSettings["TableDay4"]);
                TableMonth = int.Parse(ConfigurationManager.AppSettings["TableMonth"]);
                LineYear = int.Parse(ConfigurationManager.AppSettings["LineYear"]);
                LineMonth = int.Parse(ConfigurationManager.AppSettings["LineMonth"]);
                BarYear = int.Parse(ConfigurationManager.AppSettings["BarYear"]);
                BarMonth = int.Parse(ConfigurationManager.AppSettings["BarMonth"]);
                BarDay = int.Parse(ConfigurationManager.AppSettings["BarDay"]);

                GetDataTable(OrganizationName, TableDay1, TableDay2, TableDay3, TableDay4, TableMonth);
                GetDataLine(OrganizationName, LineYear, LineMonth);
                GetDataBar(OrganizationName, BarYear, BarMonth, BarDay);
            }
        }

        //字体、背景色
        private void bandedGridView1_RowCellStyle(object sender, RowCellStyleEventArgs e)
        {
            GridView currentView = sender as GridView;
            DataRow row = this.bandedGridView1.GetDataRow(e.RowHandle);

            //全局字体
            DevExpress.Utils.AppearanceObject.DefaultFont = new System.Drawing.Font("宋体", 10);
            //表格线
            bandedGridView1.Appearance.HorzLine.BackColor = Color.Black;
            bandedGridView1.Appearance.VertLine.BackColor = Color.Black;

            //行变色
            if (row["序号"].ToString() == "")
            {
                e.Appearance.BackColor = Color.FromArgb(89, 89, 89);
                e.Appearance.ForeColor = Color.FromArgb(255, 255, 255);
                e.Appearance.Font = new System.Drawing.Font("宋体", 10, FontStyle.Bold);
            }
            //列变色
            if (e.Column.FieldName == "品种数" || e.Column.FieldName == "常用品种数" || e.Column.FieldName == "库存总量"
                || e.Column.FieldName == "60天以上库存量" || e.Column.FieldName == "物料种类" || e.Column.FieldName == "库存量1"
                || e.Column.FieldName == "物料品种1" || e.Column.FieldName == "库存量2" || e.Column.FieldName == "物料品种2"
                || e.Column.FieldName == "库存量3" || e.Column.FieldName == "种类")
            {
                e.Appearance.ForeColor = Color.FromArgb(216, 138, 76);
                e.Appearance.Font = new System.Drawing.Font("宋体", 10, FontStyle.Bold);
            }
        }

        //不可编辑
        private void bandedGridView1_ShowingEditor(object sender, CancelEventArgs e)
        {
            GridView view = sender as GridView;
            DataRow row = this.bandedGridView1.GetDataRow(this.bandedGridView1.FocusedRowHandle);
            e.Cancel = true;
        }

        //获取表格数据方法
        public void GetDataTable(string OrganizationName, int day11, int day12, int day21, int day22, int month)
        {
            /*传入参数
            day11,day12  //时间段1
            day21,day22  //时间段2
            month    //超出月份
            */

            //修改表头时间段
            this.Times1.Caption = "" + day11 + "<= 库龄 <=" + day12 + "天";
            this.Times2.Caption = "" + day21 + "<= 库龄 <=" + day22 + "天";
            this.Times3.Caption = "库存呆滞预警物料(库龄超" + month + "个月)";

            try
            {
                #region 执行存储过程
                //打开数据库连接
                conn.Open();

                string sql = "exec Stock_Age_table '"+ OrganizationName + "'," + day11 + "," + day12 + "," + day21 + "," + day22 + "," + month + "";
                SqlCommand comm = new SqlCommand(sql, conn);        //定义执行SQL语句的对象，并将SQL语句赋予对象
                SqlDataAdapter da = new SqlDataAdapter(comm);       //执行连接

                DataSet ds = new DataSet();
                da.Fill(ds);                                        //将数据存入数据容器
                conn.Close();

                if (ds.Tables[0].Rows.Count <= 0)
                {
                    //MessageBox.Show("库龄分析表 查询数据为空值");
                    return;
                }

                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                #endregion

                #region 添加序号列
                //添加序列号
                int rowLength = dt.Rows.Count;
                int colLength = dt.Columns.Count;
                DataRow[] newRows = new DataRow[rowLength];
                DataTable dtNew = new DataTable();
                dtNew.Columns.Add("序号");    //在第一列添加序号

                for (int i = 0; i < colLength; i++)
                {
                    dtNew.Columns.Add(dt.Columns[i].ColumnName);
                    //复制dt中的数据到newRows，保留表相同的架构
                    for (int j = 0; j < rowLength; j++)
                    {
                        if (newRows[j] == null)
                            newRows[j] = dtNew.NewRow();
                        //将其他数据填充到第二列之后，因为第一列为新增的序号列
                        newRows[j][i + 1] = dt.Rows[j][i];
                    }
                }
                foreach (DataRow row in newRows)
                {
                    dtNew.Rows.Add(row);    //赋值给dtNew
                }
                //根据行数填充序号
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dtNew.Rows[i]["序号"] = i + 1;
                }
                #endregion

                #region 添加合计行，计算每列数据
                //合计行
                DataRow dr = dtNew.NewRow();
                dr["仓库名称"] = "合计";
                for (int j = 1; j < dt.Columns.Count; j++)
                {
                    float value = 0;  //单个数据值
                    float sum = 0;  //数据总和
                    string returnstr = "";  //列字段返回值
                    string str = "%";  //数据中的特殊字符

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //判断是否存在百分号，如果存在删除百分号,将数据转化为浮点型形
                        string x1 = dt.Rows[i][j].ToString();
                        if (x1.Contains(str))
                        {
                            string result = x1.Replace(str, "");  //去除%号
                            value = float.Parse(result);
                            sum += value;
                        }
                        else
                        {
                            value = int.Parse(x1);
                            sum += value;
                        }
                    }

                    //获取列字段名,从dt中拿
                    returnstr = dt.Columns[j].ColumnName;

                    //判断列数据是否需要添加%号
                    if (returnstr.Contains(str))
                    {
                        int intsum = (int)(sum / dt.Rows.Count + 0.5f);  //取整
                        dr[returnstr] = intsum.ToString() + str;  //算出平均值，并添加%号
                    }
                    else
                    {
                        dr[returnstr] = sum;
                    }
                }

                dtNew.Rows.Add(dr);  //在dtNew表中添加合计行
                #endregion

                dtViewAll.Clear();
                dtViewAll = dtNew;

                //分页显示
                dtView = dtViewAll.Clone();
                if (dtViewAll.Rows.Count <= dtViewRowsIndex)
                {
                    dtViewRowsIndex = dtView.Rows.Count;
                    for (int i = 0; i < dtViewRowsIndex; i++)
                    {
                        dtView.Rows.Add(dtViewAll.Rows[i].ItemArray); //添加数据行
                    }

                    gridControl1.DataSource = dtView;
                    gridControl1.ForceInitialize();
                }
                else
                {
                    for (int i = 0; i < dtViewRowsIndex; i++)
                    {
                        dtView.Rows.Add(dtViewAll.Rows[i].ItemArray); //添加数据行
                    }

                    gridControl1.DataSource = dtView;
                    gridControl1.ForceInitialize();

                    LineNow = dtViewRowsIndex;
                    TimerPage.Enabled = true;
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message );
            }
        }

        //获取折线图数据方法
        [Obsolete]
        public void GetDataLine(string OrganizationName, int year,int month)
        {
            /*传入参数
            OrganizationName    //组织名称
            year  //年份
            month    //超出月份
            */

            try
            {
                //打开数据库连接
                conn.Open();

                //清理表数据
                this.chartControl1.Titles.Clear();
                this.chartControl1.Series.Clear();

                //获取stockidArray数组
                string stockSQL = "exec Stock_YearOverAge_table '" + OrganizationName + "'," + year + "," + month + ",''";
                var stockidArray = StockIDArray(stockSQL);

                //如果数据为空，返回无呆滞物资和空数据
                if (stockidArray.Length <= 0)
                {
                    //空表
                    DataTable dt = new DataTable();
                    dt.Columns.Add("月份", typeof(string));
                    dt.Columns.Add("不常用品种数", typeof(int));
                    var dtNew = CompleteYearData(dt, stockidArray);

                    Series sr = new Series("", ViewType.Bar);
                    //设置Series样式
                    sr.ArgumentScaleType = ScaleType.Qualitative;//定性的
                    sr.ValueScaleType = ScaleType.Numerical;//数字类型
                    sr.PointOptions.PointView = PointView.ArgumentAndValues;//显示表示的信息和数据
                    //绑定数据源
                    sr.DataSource = dtNew.DefaultView;//newdtb是获取到的数据(可以是数据库中的表，也可以是DataTable)
                    sr.ArgumentDataMember = "月份";//绑定的文字信息（名称）(坐标横轴)
                    sr.ValueDataMembers[0] = "不常用品种数";//绑定的值（数据）(坐标纵轴)
                    this.chartControl1.Series.Add(sr);

                    conn.Close();
                    ChartTitle ctnull = new ChartTitle
                    {
                        Text = "" + year + "年无呆滞物资",
                        TextColor = Color.FromArgb(0, 128, 0),//颜色
                        Font = new Font("Tahoma", 14),//字体
                        Dock = ChartTitleDockStyle.Top,//停靠在上方
                        Alignment = StringAlignment.Center//居中显示
                    };
                    this.chartControl1.Titles.Add(ctnull);
                    chartControl1.Legend.Visible = true;//不现实指示图
                    return;
                }

                #region 循环stockidArray数组
                for (int i = 0; i < stockidArray.Length; i++)
                {
                    #region 执行存储过程
                    int stockid = stockidArray[i];
                    string sql = "exec Stock_YearOverAge_table '" + OrganizationName + "'," + year + "," + month + "," + stockid + "";
                    SqlCommand comm = new SqlCommand(sql, conn);        //定义执行SQL语句的对象，并将SQL语句赋予对象
                    SqlDataAdapter da = new SqlDataAdapter(comm);       //执行连接
                    DataSet ds = new DataSet();
                    da.Fill(ds);                                        //将数据存入数据容器
                    DataTable dt = new DataTable();
                    dt = ds.Tables[0];
                    if (dt.Rows.Count<=0)
                    {
                        continue;   //如果数据为空，跳出当前循环
                    }
                    #endregion

                    //补全12个月数据表
                    var dtNew = CompleteYearData(dt, stockidArray);

                    #region 新建Line
                    Series sr = new Series(dtNew.Rows[0]["仓库名称"].ToString() + stockid.ToString(), ViewType.Line);//名称与图标的类型

                    //设置Series样式
                    sr.ArgumentScaleType = ScaleType.Qualitative;//定性的
                    sr.ValueScaleType = ScaleType.Numerical;//数字类型
                    sr.PointOptions.PointView = PointView.ArgumentAndValues;//显示表示的信息和数据

                    //绑定数据源
                    sr.DataSource = dtNew.DefaultView;//newdtb是获取到的数据(可以是数据库中的表，也可以是DataTable)
                    sr.ArgumentDataMember = "月份";//绑定的文字信息（名称）(坐标横轴)
                    sr.ValueDataMembers[0] = "不常用品种数";//绑定的值（数据）(坐标纵轴)

                    //在上方显示数值
                    //sr.LabelsVisibility = DevExpress.Utils.DefaultBoolean.True;

                    //添加到统计图上
                    this.chartControl1.Series.Add(sr);
                    #endregion
                }
                #endregion
                conn.Close();

                //图例设置
                SimpleDiagram3D diagram = new SimpleDiagram3D
                {
                    RuntimeRotation = true,
                    RuntimeScrolling = true,
                    RuntimeZooming = true
                };

                //设置图表标题
                ChartTitle ct = new ChartTitle
                {
                    Text = "" + year + "年呆滞物资趋势统计",
                    TextColor = Color.FromArgb(86, 86, 86),//颜色
                    Font = new Font("Tahoma", 12),//字体
                    Dock = ChartTitleDockStyle.Top,//停靠在上方
                    Alignment = StringAlignment.Center//居中显示
                };
                this.chartControl1.Titles.Add(ct);
                chartControl1.Legend.Visible = true;//不现实指示图
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                conn.Close();
            }
        }

        //获取柱形图数据方法
        [Obsolete]
        public void GetDataBar(string OrganizationName, int year,int month,int day)
        {
            /*传入参数
            OrganizationName    //组织名称
            year    //年度
            month    //月份
            day     //超出天数
            */

            try
            {
                //打开数据库连接
                conn.Open();

                //清理表数据
                this.chartControl2.Titles.Clear();
                this.chartControl2.Series.Clear();

                //获取stockidArray数组
                string stockSQL = "exec Stock_MonthOverAge_table '" + OrganizationName + "'," + year + "," + month + "," + day + ",''";
                var stockidArray = StockIDArray(stockSQL);
                //如果数据为空，返回无超龄数量和空数据
                if (stockidArray.Length <= 0)
                {
                    //空表
                    Series sr = new Series("", ViewType.Bar);
                    this.chartControl2.Series.Add(sr);

                    conn.Close();
                    ChartTitle ctnull = new ChartTitle
                    {
                        Text = "" + year + "年" + month + "月份各仓库无超龄数量",
                        TextColor = Color.FromArgb(0, 128, 0),//颜色
                        Font = new Font("Tahoma", 14),//字体
                        Dock = ChartTitleDockStyle.Top,//停靠在上方
                        Alignment = StringAlignment.Center//居中显示
                    };
                    this.chartControl2.Titles.Add(ctnull);
                    chartControl2.Legend.Visible = true;//不现实指示图
                    return;
                }

                for (int i = 0; i < stockidArray.Length; i++)
                {
                    #region 执行存储过程
                    int stockid = stockidArray[i];
                    string sql = "exec Stock_MonthOverAge_table '" + OrganizationName + "'," + year + "," + month + "," + day + "," + stockid + "";
                    SqlCommand comm = new SqlCommand(sql, conn);        //定义执行SQL语句的对象，并将SQL语句赋予对象
                    SqlDataAdapter da = new SqlDataAdapter(comm);       //执行连接
                    DataSet ds = new DataSet();
                    da.Fill(ds);                                        //将数据存入数据容器
                    DataTable dt = new DataTable();
                    dt = ds.Tables[0];
                    if (dt.Rows.Count <= 0)
                    {
                        continue;   //如果数据为空，跳出当前循环
                    }
                    #endregion

                    #region 新建Bar
                    Series sr = new Series("", ViewType.Bar);//名称与图标的类型

                    //设置Series样式
                    sr.ArgumentScaleType = ScaleType.Qualitative;//定性的
                    sr.ValueScaleType = ScaleType.Numerical;//数字类型
                    sr.PointOptions.PointView = PointView.ArgumentAndValues;//显示表示的信息和数据

                    //绑定数据源
                    sr.DataSource = dt.DefaultView;//newdtb是获取到的数据(可以是数据库中的表，也可以是DataTable)
                    sr.ArgumentDataMember = "仓库名称";//绑定的文字信息（名称）(坐标横轴)
                    sr.ValueDataMembers[0] = "不常用品种数";//绑定的值（数据）(坐标纵轴)

                    //在上方显示数值
                    BarSeriesLabel label = (BarSeriesLabel)sr.Label;
                    label.Position = BarSeriesLabelPosition.Top;
                    sr.LabelsVisibility = DevExpress.Utils.DefaultBoolean.True;

                    //添加到统计图上
                    this.chartControl2.Series.Add(sr);
                    #endregion
                }
                conn.Close();

                //图例设置
                SimpleDiagram3D diagram = new SimpleDiagram3D();
                diagram.RuntimeRotation = true;
                diagram.RuntimeScrolling = true;
                diagram.RuntimeZooming = true;

                //设置图表标题
                ChartTitle ct = new ChartTitle();
                ct.Text = "" + year + "年" + month + "月份各仓库超龄种类统计";
                ct.TextColor = Color.FromArgb(86, 86, 86);//颜色
                ct.Font = new Font("Tahoma", 12);//字体
                ct.Dock = ChartTitleDockStyle.Top;//停靠在上方
                ct.Alignment = StringAlignment.Center;//居中显示
                this.chartControl2.Titles.Add(ct);
                chartControl2.Legend.Visible = true;//不现实指示图
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                conn.Close();
            }
        }

        //获取仓库ID数组方法
        public int[] StockIDArray(string stockSQL)
        {
            #region 获取Stockid数组
            //执行存储过程，取到有数据的仓库id
            SqlCommand commStock = new SqlCommand(stockSQL, conn);        //定义执行SQL语句的对象，并将SQL语句赋予对象
            SqlDataAdapter Stockid = new SqlDataAdapter(commStock);       //执行连接
            DataSet dsStock = new DataSet();
            Stockid.Fill(dsStock);
            DataTable dtStock = new DataTable();
            dtStock = dsStock.Tables[0];

            //将stockid传进list赋值给数组
            List<int> liststock = new List<int>();
            for (int i = 0; i < dtStock.Rows.Count; i++)
            {
                liststock.Add(int.Parse(dtStock.Rows[i]["FSTOCKID"].ToString()));
            }
            List<int> listNew = new List<int>();

            //删除重复仓位id
            foreach (int eachString in liststock)
            {
                if (!listNew.Contains(eachString))
                    listNew.Add(eachString);
            }
            int[] stockID = listNew.ToArray();
            #endregion

            return stockID;
        }

        //补全仓库12个月数据
        public DataTable CompleteYearData(DataTable dt, int[] stockidArray)
        {
            /*传入参数
            dt    //stockid本年度数据表
            stockidArray    //stockid集合，如果为空配置空数据
            */
            #region 补全12个月数据并排序
            for (int m = 1; m <= 12; m++)
            {
                //月份需补全0才能正确排序
                String monthname;
                if (m < 10)
                {
                    monthname = "0" + m.ToString() + "月";
                }
                else
                {
                    monthname = m.ToString() + "月";
                }

                //判断是否存在当月数据，不存在插入一行当月数据
                DataRow[] dataRows = dt.Select("月份" + "='" + monthname + "'");
                if (dataRows.Length > 0)
                {
                    continue;
                }
                if (dataRows.Length <= 0 && stockidArray.Length <= 0)
                {
                    DataRow newRow = dt.NewRow();
                    newRow["月份"] = monthname;
                    newRow["不常用品种数"] = 0;
                    dt.Rows.Add(newRow);
                }
                else
                {
                    DataRow newRow = dt.NewRow();
                    newRow["组织名称"] = dt.Rows[0][0];
                    newRow["仓库名称"] = dt.Rows[0][1];
                    newRow["FSTOCKID"] = dt.Rows[0][2];
                    newRow["月份"] = monthname;
                    newRow["不常用品种数"] = 0;
                    dt.Rows.Add(newRow);
                }


            }
            dt.DefaultView.Sort = "月份 ASC";//按月份倒序
            dt = dt.DefaultView.ToTable();//返回一个新的DataTable
            #endregion

            return dt;
        }

        //合并单元格
        public void MergeCell(DataTable dt)
        {
            //单元格横向合并
            MyGridViewHandler ViewHandler;
            ViewHandler = new MyGridViewHandler(bandedGridView1);
            //保留值，行号，需合并的列
            ViewHandler.MergeCells(bandedGridView1.GetRowCellValue(dt.Rows.Count - 1, "仓库名称").ToString(), bandedGridView1.GetDataSourceRowIndex(dt.Rows.Count - 1), new GridColumn[] { bandedGridView1.Columns[0], bandedGridView1.Columns[1] });
        }
    }
}